-- All standard apr.db tests will require the sqlite driver be loaded and available
require 'moonunit'
require 'apr.db'

test_db = moonunit.TestCase:new()

function test_db:_test_getdbconn()
    local conn = apr.db.connect('sqlite://')
    conn:exec('create table t_test(t_field TEXT, i_field INTEGER)')
    return conn
end

function test_db:test_connect()
    -- connect to an in-memory sqlite database
    local conn = apr.db.connect('sqlite://')
    assert(conn.driver == 'sqlite3')
    assert(conn.connected)
    assert(conn:check())
    
    conn:close()
    assert(not conn.connected)
    assert(not conn:check())
end

function test_db:test_exec()
    local conn = self:_test_getdbconn()
    local affected = conn:exec('insert into t_test (t_field, i_field) values (?, ?)', "test field", 15)
    assert(affected == 1)
    
    local result = conn:select('select * from t_test')
    for row in result:rows() do
        assert(row.t_field == 'test field')
        assert(row.i_field == 15)
    end
    
    result:close()
    conn:close()
end

function test_db:test_zeroconv()
    -- make sure that the base library is converting the right numeric strings to numbers
    local conn = self:_test_getdbconn()
    conn:exec('insert into t_test (t_field, i_field) values (?, ?)', "00056", 0)
    for row in conn:select('select * from t_test'):rows() do
        assert(row.t_field == "00056")
        assert(row.i_field == 0)
    end
    
    conn:exec('delete from t_test');
    conn:exec('insert into t_test (t_field, i_field) values (?, ?)', "   5 ", 2);
    for row in conn:select('select * from t_test'):rows() do
        assert(row.t_field == "   5 ")
    end
end

function test_db:test_irows()
    local conn = self:_test_getdbconn()
    conn:exec('insert into t_test (t_field, i_field) values(?, ?)', 'hello', 4)
    for row in conn:select('select * from t_test'):irows() do
        assert(row[1] == 'hello')
        assert(row[2] == 4)
    end
end

function test_db:test_insert()
    local conn = self:_test_getdbconn()
    
    conn:insert('t_test', {t_field = 'hello world', i_field = 11})
    for row in conn:select('select * from t_test'):rows() do
        assert(row.t_field == 'hello world')
        assert(row.i_field == 11)
    end
end

function test_db:test_update()
    local conn = self:_test_getdbconn()
    
    conn:insert('t_test', {t_field = 'hello world', i_field = 11})
    conn:insert('t_test', {t_field = 'hello world3', i_field = 4})
    conn:update('t_test', {t_field = 'world2'})
    for row in conn:select('select * from t_test'):rows() do assert(row.t_field == 'world2') end
    conn:update('t_test', {t_field = 'world3'}, {i_field = 4})
    for row in conn:select('select * from t_test where i_field=4'):rows() do assert(row.t_field == 'world3') end
    conn:update('t_test', {t_field = 'world4'}, 'i_field=4')
    for row in conn:select('select * from t_test where i_field=4'):rows() do assert(row.t_field == 'world4') end
    conn:update('t_test', {t_field = 'world5'}, 'i_field=?', 4)
    for row in conn:select('select * from t_test where i_field=4'):rows() do assert(row.t_field == 'world5') end
end

function test_db:test_prepared_exec()
    local conn = self:_test_getdbconn()
    local stmt = conn:prepare('insert into t_test (t_field, i_field) values (?, ?)')
    local affected = stmt:exec('one', 1)
    assert(affected == 1)
    
    for row in conn:select('select * from t_test'):rows() do assert(row.t_field == 'one' and row.i_field == 1) end
    
    stmt:exec('two', 2)
    local res = conn:select('select * from t_test where i_field=2')
    assert(res.numrows == 1)
    for row in res:rows() do assert(row.t_field == 'two' and row.i_field == 2) end
    
    local upstmt = conn:prepare('update t_test set t_field=?')
    assert(upstmt:exec('number') == 2)
    
    for row in conn:select('select * from t_test'):rows() do assert(row.t_field == 'number') end
end

function test_db:tset_prepared_select()
    local conn = self:_test_getdbconn()
    conn:insert('t_test', {t_field = 'hello world', i_field = 1})
    conn:insert('t_test', {t_field = 'noshow', i_field = 2})
    
    local stmt = conn:prepare('select * from t_test where i_field=?')
    local res = stmt:select(1)
    assert(res.numrows == 1)
    for row in res:rows() do assert(row.t_field == 'hello world') end
end
